﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;



public class clsData
{

    SqlConnection scon = new SqlConnection();
    public clsData(SqlConnection _scon)
    {
        scon = _scon;
    }

    #region "   ExecuteSQL"
    public DataTable ExecuteSQLGet(string _StrSQL)
    {
        DataTable tb = new DataTable();
        try
        {
            scon.Open();
            SqlCommand cmd = new SqlCommand(_StrSQL, scon);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tb);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        if (scon.State == ConnectionState.Open)
            scon.Close();
        return tb;
    }

    public int ExecuteSQLUpdate(string _StrSQL)
    {
        int iresult = 0;
        try
        {
            scon.Open();
            SqlCommand cmd = new SqlCommand(_StrSQL, scon);
            iresult = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
        if (scon.State == ConnectionState.Open)
            scon.Close();
        return iresult;
    }
    #endregion

    #region "   ExecuteSPUpdate"
    public int ExecuteSPUpdate(string spName, string[] param, object[] values, ref string _ioutput)
    {//                                                                         string _ioutput = "@ireturn";
        int result = 0;
        try
        {
            scon.Open();
            SqlCommand cmd = new SqlCommand(spName, scon);
            cmd.CommandType = CommandType.StoredProcedure;

            if (param != null)
            {
                for (int i = 0; i < param.Length; i++)
                {
                    if (param[i] + "" != "")
                        cmd.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
            }
            //cmd.Parameters.Add(new SqlParameter(param[i], values[i]));
            if (_ioutput != "")
            {
                if (_ioutput.IndexOf("@") != 0)
                    _ioutput = "@" + _ioutput;
                ///
                SqlParameter parm3 = new SqlParameter(_ioutput, SqlDbType.NVarChar);
                parm3.Size = 300;
                parm3.Value = "hiệu.lê";
                parm3.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parm3);
            }

            result = cmd.ExecuteNonQuery();
            if (_ioutput != "")
            {
                object Tuine = cmd.Parameters["" + _ioutput].Value;
                _ioutput = Tuine + "";
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
        if (scon.State == ConnectionState.Open)
            scon.Close();
        return result;


    }
    public int ExecuteSPUpdate(string spName, string[] param, object[] values)
    {
        int result = 0;
        try
        {
            scon.Open();
            SqlCommand cmd = new SqlCommand(spName, scon);
            cmd.CommandType = CommandType.StoredProcedure;

            if (param != null)
            {
                for (int i = 0; i < param.Length; i++)
                {
                    if (param[i] + "" != "")
                        cmd.Parameters.Add(new SqlParameter(param[i], values[i]));
                }
            }
            result = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
        if (scon.State == ConnectionState.Open)
            scon.Close();
        return result;
    }
    #endregion

    #region "   ExecuteSPGet"
    public DataTable ExecuteSPGet(string spName, string[] param, object[] value)
    {
        DataTable dataTable = new DataTable();
        try
        {
            scon.Open();
            SqlCommand cmd = new SqlCommand(spName, scon);
            cmd.CommandType = CommandType.StoredProcedure;

            if (param != null)
            {
                for (int i = 0; i < param.Length; i++)
                {
                    if (param[i] + "" != "")
                        cmd.Parameters.Add(new SqlParameter(param[i], value[i]));
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dataTable);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        if (scon.State == ConnectionState.Open)
            scon.Close();
        return dataTable;

    }
    public DataSet ExecuteSPGetDataSet(string spName, string[] param, object[] value)
    {
        DataSet dataset = new DataSet();
        try
        {
            scon.Open();
            SqlCommand cmd = new SqlCommand(spName, scon);
            cmd.CommandType = CommandType.StoredProcedure;

            if (param != null)
            {
                for (int i = 0; i < param.Length; i++)
                {
                    if (param[i] + "" != "")
                        cmd.Parameters.Add(new SqlParameter(param[i], value[i]));
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dataset);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        if (scon.State == ConnectionState.Open)
            scon.Close();
        return dataset;

    }
    #endregion

    #region "   SP_Updatetable"
    public int SP_Updatetable(string table_name, DataTable tb)
    {
        //if (scon.State != ConnectionState.Open)
        //    scon.Open();
        int irt = 0;
        SqlDataAdapter da = new SqlDataAdapter();
        try
        {
            da.SelectCommand = new SqlCommand("Select * from " + table_name, scon);
            da.TableMappings.Clear();
            da.TableMappings.Add("Table", table_name);
            SqlCommandBuilder objbuilder = new SqlCommandBuilder(da);

            if (tb.Rows.Count > 0)
            {
                irt = da.Update(tb);
                if (irt > 0)
                    tb.GetChanges();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return irt;

    }

    int SP_Updatetable(DataTable tb)
    {
        if (scon.State != ConnectionState.Open)
            scon.Open();

        //SqlDataAdapter da = new SqlDataAdapter();

        //da.SelectCommand = new SqlCommand("Select * from " + table_name, scon);
        //da.TableMappings.Clear();
        //da.TableMappings.Add("Table", table_name);
        //SqlCommandBuilder objbuilder = new SqlCommandBuilder(da);
        //if (tb.Rows.Count > 0)
        //{
        //    if (da.Update(tb) > 0)
        //    {
        //        tb.GetChanges();
        //        return 1;
        //    }
        //}

        return -1;

    }

    #endregion

}